ALTER TABLE purchase_item ADD COLUMN id_service integer;
ALTER TABLE purchase_item ADD COLUMN "name" character varying(255);
ALTER TABLE purchase_item ADD COLUMN code character varying(20);
ALTER TABLE purchase_item ADD COLUMN item_price numeric;
ALTER TABLE purchase_item ADD COLUMN discount numeric;
ALTER TABLE purchase_item ADD COLUMN plan_delivery_time timestamp without time zone;
ALTER TABLE purchase_item ADD COLUMN real_delivery_time timestamp without time zone;
ALTER TABLE purchase_item ADD COLUMN requester integer;
ALTER TABLE purchase_item ADD COLUMN description character varying;
ALTER TABLE purchase_item ADD COLUMN remark character varying;
ALTER TABLE purchase_item ADD COLUMN id_project integer;
ALTER TABLE purchase_item ADD COLUMN id_task integer;
ALTER TABLE purchase_item ADD COLUMN id_quotation integer;
ALTER TABLE purchase_item ADD COLUMN id_quotation_item integer;
ALTER TABLE purchase_item ADD COLUMN additional_cost numeric;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_id_service_fkey FOREIGN KEY (id_service)

      REFERENCES service (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_requester_fkey FOREIGN KEY (requester)

      REFERENCES employee (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_id_project_fkey FOREIGN KEY (id_project)

      REFERENCES project (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_id_task_fkey FOREIGN KEY (id_task)

      REFERENCES checklist_item (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_id_quotation_fkey FOREIGN KEY (id_quotation)

      REFERENCES quotation (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE purchase_item ADD CONSTRAINT purchase_id_quotation_item_fkey FOREIGN KEY (id_quotation_item)

      REFERENCES quotation_item (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION;
	  
	  
DROP FUNCTION sp_purchase_item_count(integer, integer, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, numeric, integer, integer, numeric, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_purchase_item_insert(integer, integer, timestamp without time zone, timestamp without time zone, numeric, integer, integer, numeric, character varying, integer, boolean, integer, integer);
DROP FUNCTION sp_purchase_item_selectlist(integer, integer, integer, integer, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, numeric, integer, integer, numeric, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_purchase_item_update(integer, integer, integer, timestamp without time zone, timestamp without time zone, numeric, integer, integer, numeric, character varying, integer, boolean, integer, integer, boolean, timestamp without time zone);

CREATE OR REPLACE FUNCTION sp_purchase_item_insert(
	_id_purchase	integer DEFAULT null,
	_id_product	integer DEFAULT null,
	_from_date	timestamp without time zone DEFAULT null,
	_to_date	timestamp without time zone DEFAULT null,
	_quantity	numeric DEFAULT null,
	_id_unit	integer DEFAULT null,
	_unit_price	integer DEFAULT null,
	_total_price	numeric DEFAULT null,
	_note	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_id_service	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_item_price	numeric DEFAULT null,
	_discount	numeric DEFAULT null,
	_plan_delivery_time	timestamp without time zone DEFAULT null,
	_real_delivery_time	timestamp without time zone DEFAULT null,
	_requester	integer DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_task	integer DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_additional_cost	numeric DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO purchase_item(id_purchase,id_product,from_date,to_date,quantity,id_unit,unit_price,total_price,note,status,is_draft,notification,creator,updator,deletion_time,id_service,name,code,item_price,discount,plan_delivery_time,real_delivery_time,requester,description,remark,id_project,id_task,id_quotation,id_quotation_item,additional_cost)
	VALUES(_id_purchase,_id_product,_from_date,_to_date,_quantity,_id_unit,_unit_price,_total_price,_note,_status,_is_draft,_notification,_creator,_creator,null,_id_service,_name,_code,_item_price,_discount,_plan_delivery_time,_real_delivery_time,_requester,_description,_remark,_id_project,_id_task,_id_quotation,_id_quotation_item,_additional_cost);
RETURN currval('purchase_item_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_purchase_item_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_id_purchase	integer DEFAULT null,
	_id_product	integer DEFAULT null,
	_from_dateFrom	timestamp without time zone DEFAULT null,
	_from_dateTo	timestamp without time zone DEFAULT null,
	_to_dateFrom	timestamp without time zone DEFAULT null,
	_to_dateTo	timestamp without time zone DEFAULT null,
	_quantity	numeric DEFAULT null,
	_id_unit	integer DEFAULT null,
	_unit_price	integer DEFAULT null,
	_total_price	numeric DEFAULT null,
	_note	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_id_service	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_item_price	numeric DEFAULT null,
	_discount	numeric DEFAULT null,
	_plan_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_plan_delivery_timeTo	timestamp without time zone DEFAULT null,
	_real_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_real_delivery_timeTo	timestamp without time zone DEFAULT null,
	_requester	integer DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_task	integer DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_additional_cost	numeric DEFAULT null
)
RETURNS SETOF purchase_item
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM purchase_item WHERE
	 	(_id is null OR id = _id)	
	AND (_id_purchase is null OR id_purchase = _id_purchase)	
	AND (_id_product is null OR id_product = _id_product)	
	AND (_from_dateFrom is null OR from_date >= _from_dateFrom)
	AND	(_from_dateTo is null OR from_date <= _from_dateTo)	
	AND (_to_dateFrom is null OR to_date >= _to_dateFrom)
	AND	(_to_dateTo is null OR to_date <= _to_dateTo)	
	AND (_quantity is null OR quantity = _quantity)	
	AND (_id_unit is null OR id_unit = _id_unit)	
	AND (_unit_price is null OR unit_price = _unit_price)	
	AND (_total_price is null OR total_price = _total_price)	
	AND (_note is null OR note = _note)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_id_service is null OR id_service = _id_service)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_item_price is null OR item_price = _item_price)	
	AND (_discount is null OR discount = _discount)	
	AND (_plan_delivery_timeFrom is null OR plan_delivery_time >= _plan_delivery_timeFrom)
	AND	(_plan_delivery_timeTo is null OR plan_delivery_time <= _plan_delivery_timeTo)	
	AND (_real_delivery_timeFrom is null OR real_delivery_time >= _real_delivery_timeFrom)
	AND	(_real_delivery_timeTo is null OR real_delivery_time <= _real_delivery_timeTo)	
	AND (_requester is null OR requester = _requester)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_task is null OR id_task = _id_task)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_quotation_item is null OR id_quotation_item = _id_quotation_item)	
	AND (_additional_cost is null OR additional_cost = _additional_cost)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_purchase_item_count (
	_id	integer DEFAULT null,
	_id_purchase	integer DEFAULT null,
	_id_product	integer DEFAULT null,
	_from_dateFrom	timestamp without time zone DEFAULT null,
	_from_dateTo	timestamp without time zone DEFAULT null,
	_to_dateFrom	timestamp without time zone DEFAULT null,
	_to_dateTo	timestamp without time zone DEFAULT null,
	_quantity	numeric DEFAULT null,
	_id_unit	integer DEFAULT null,
	_unit_price	integer DEFAULT null,
	_total_price	numeric DEFAULT null,
	_note	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_id_service	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_item_price	numeric DEFAULT null,
	_discount	numeric DEFAULT null,
	_plan_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_plan_delivery_timeTo	timestamp without time zone DEFAULT null,
	_real_delivery_timeFrom	timestamp without time zone DEFAULT null,
	_real_delivery_timeTo	timestamp without time zone DEFAULT null,
	_requester	integer DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_task	integer DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_quotation_item	integer DEFAULT null,
	_additional_cost	numeric DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM purchase_item WHERE
	 	(_id is null OR id = _id)	
	AND (_id_purchase is null OR id_purchase = _id_purchase)	
	AND (_id_product is null OR id_product = _id_product)	
	AND (_from_dateFrom is null OR from_date >= _from_dateFrom)
	AND	(_from_dateTo is null OR from_date <= _from_dateTo)	
	AND (_to_dateFrom is null OR to_date >= _to_dateFrom)
	AND	(_to_dateTo is null OR to_date <= _to_dateTo)	
	AND (_quantity is null OR quantity = _quantity)	
	AND (_id_unit is null OR id_unit = _id_unit)	
	AND (_unit_price is null OR unit_price = _unit_price)	
	AND (_total_price is null OR total_price = _total_price)	
	AND (_note is null OR note = _note)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_id_service is null OR id_service = _id_service)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_item_price is null OR item_price = _item_price)	
	AND (_discount is null OR discount = _discount)	
	AND (_plan_delivery_timeFrom is null OR plan_delivery_time >= _plan_delivery_timeFrom)
	AND	(_plan_delivery_timeTo is null OR plan_delivery_time <= _plan_delivery_timeTo)	
	AND (_real_delivery_timeFrom is null OR real_delivery_time >= _real_delivery_timeFrom)
	AND	(_real_delivery_timeTo is null OR real_delivery_time <= _real_delivery_timeTo)	
	AND (_requester is null OR requester = _requester)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_task is null OR id_task = _id_task)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_quotation_item is null OR id_quotation_item = _id_quotation_item)	
	AND (_additional_cost is null OR additional_cost = _additional_cost);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_purchase_item_update 
(	_id integer,
	_id_purchase integer,
	_id_product integer,
	_from_date timestamp without time zone,
	_to_date timestamp without time zone,
	_quantity numeric,
	_id_unit integer,
	_unit_price integer,
	_total_price numeric,
	_note character varying,
	_status integer,
	_is_draft boolean,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_id_service integer,
	_name character varying(255),
	_code character varying(20),
	_item_price numeric,
	_discount numeric,
	_plan_delivery_time timestamp without time zone,
	_real_delivery_time timestamp without time zone,
	_requester integer,
	_description character varying,
	_remark character varying,
	_id_project integer,
	_id_task integer,
	_id_quotation integer,
	_id_quotation_item integer,
	_additional_cost numeric  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE purchase_item 
	SET 		id_purchase = _id_purchase,
		id_product = _id_product,
		from_date = _from_date,
		to_date = _to_date,
		quantity = _quantity,
		id_unit = _id_unit,
		unit_price = _unit_price,
		total_price = _total_price,
		note = _note,
		status = _status,
		is_draft = _is_draft,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		id_service = _id_service,
		name = _name,
		code = _code,
		item_price = _item_price,
		discount = _discount,
		plan_delivery_time = _plan_delivery_time,
		real_delivery_time = _real_delivery_time,
		requester = _requester,
		description = _description,
		remark = _remark,
		id_project = _id_project,
		id_task = _id_task,
		id_quotation = _id_quotation,
		id_quotation_item = _id_quotation_item,
		additional_cost = _additional_cost
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
